# -*- coding:utf-8 -*-
import MySQLdb


class MySQLDBHelper:
    def __init__(self):
        self.host = '127.0.0.1'
        self.user = 'test2'
        self.passwd = 'test2'
        self.db = 'spider_page'
        self.table = 'gov_info'
        self.port = 3306
        self.conn = None
        self.cur = None
        pass

    def createDB(self):
        try:
            self.conn = MySQLdb.connect(host=self.host, user=self.user, passwd=self.passwd, port=self.port,charset='utf8')
            if self.cur == None :
                self.cur = self.conn.cursor()

            self.cur.execute('create database if not exists ' + self.db)
            self.conn.select_db(self.db)

            self.drop_table()
            #TODO:change to time type
            self.cur.execute('create table if not exists '+self.table+ '(id int AUTO_INCREMENT PRIMARY KEY primary key,buyerName varchar(512),projectName varchar(512),money double,time varchar(512))')

            # values = ["邹城市妇幼保健院","邹城市妇幼保健院新生儿呼吸机设备",'270000',"2013年1月22日"]
            # self.insertIntoDB(values)
            self.conn.commit()

        except MySQLdb.Error,e:
            print "Mysql Error %d: %s" % (e.args[0], e.args[1])


    #id,buyerName,projectName,money,time
    def insertIntoDB(self,values):
        try:
            if self.cur == None :
                self.cur = self.conn.cursor()
            self.cur.execute('insert into ' + self.table + '(buyerName,projectName,money,time) values(%s,%s,%s,%s)',values)
        except MySQLdb.Error, e:
            print "Mysql Error %d: %s" % (e.args[0], e.args[1])

    def initDB(self):
        try:
            self.createDB()

        except MySQLdb.Error, e:
            print "Mysql Error %d: %s" % (e.args[0], e.args[1])
            self.closeDB()


    def closeDB(self):
        if self.cur == None or self.cur == None:
            print 'None'
            return

        try:
            self.cur.close()
            self.conn.close()
        except MySQLdb.Error,e:
            print "Mysql Error %d: %s" % (e.args[0], e.args[1])

    #return list[tuple,tuple]
    def selectAll(self):
        try:
            count = self.cur.execute('select * from '+self.table)
            print '%s rows ', count
            result = self.cur.fetchmany(count)
            return result

        except MySQLdb.Error, e:
            print "Mysql Error %d: %s" % (e.args[0], e.args[1])

    def testPrint(self):
        try:
            result = self.selectAll()
            for r in result:
                print r

        except MySQLdb.Error, e:
            print "Mysql Error %d: %s" % (e.args[0], e.args[1])

    def deleteAllData(self):
        try:
            if self.cur == None :
                self.cur = self.conn.cursor()
            count = self.cur.execute('delete from '+self.table)
        except MySQLdb.Error, e:
            print "Mysql Error %d: %s" % (e.args[0], e.args[1])
    def drop_table(self):
        try:
            if self.cur == None :
                self.cur = self.conn.cursor()
            self.cur.execute('drop table '+self.table)
        except MySQLdb.Error, e:
            print "Mysql Error %d: %s" % (e.args[0], e.args[1])

if __name__ == '__main__':
    mysqlDBhelper = MySQLDBHelper()
    mysqlDBhelper.initDB()
    mysqlDBhelper.testPrint()
    # mysqlDBhelper.deleteAllData()
    mysqlDBhelper.closeDB()

